﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace Library
{
    public class clTaoTbSuDung
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public DataTable createtable(string month)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_taobang_sudung", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@monthsudung", SqlDbType.NVarChar, 50).Value = month;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable loadDataSuDung(string month,int page, int CountRow, Int16 idDHT, Int16 idPhuong, Int16 idP)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_sudungload", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@Thangnam", SqlDbType.NVarChar, 50).Value = month;
                    sqlcmd.Parameters.Add("@page", SqlDbType.Int).Value = page;
                    sqlcmd.Parameters.Add("@ConutRecord", SqlDbType.Int).Value = CountRow;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idpho", SqlDbType.SmallInt).Value = idP;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable listTableSudung()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("List_Table_Sudung", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int countrow(string thangnam, Int16 idDHT, Int16 idPhuong, Int16 idP)
        {
            int rtt = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_Sudung_phantrang", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@thangnam", SqlDbType.NVarChar, 50).Value = thangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idP;
                    rtt = Convert.ToInt32(sqlcmd.ExecuteScalar());
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtt;
        }

        public int tinhcuoc(Int64 idkh, Int16 somoi2, string tbsudung)
        {
            int rtt = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("hh_st_tinhcuoc", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@somoi2", SqlDbType.SmallInt).Value = somoi2;
                    sqlcmd.Parameters.Add("@tbSudung", SqlDbType.NVarChar).Value = tbsudung;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtt =1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtt;
        }

        public string full(DataTable listsudung)
        {
            string str = "";
                listsudung = listTableSudung();
                DataRow dtr = listsudung.Rows[0];
                str = dtr["name"].ToString();
                str = str.Substring(8,str.Length-8);
                for (int i = 1; i < listsudung.Rows.Count; i++)
                {
                    DataRow dr = listsudung.Rows[i];
                    string stri = dr["name"].ToString();
                    stri = stri.Substring(8, stri.Length-8);
                    int strnam1 = int.Parse(str.Substring(0,4));
                    int strthang1 = int.Parse(str.Substring(4, str.Length - 4));
                    int strnam2 = int.Parse(stri.Substring(0,4));
                    int strthang2 = int.Parse(stri.Substring(4, stri.Length - 4));
                    if (strnam1 == strnam2 && strthang1 < strthang2)
                    {
                        str = stri;
                    }
                    else
                    {
                        if (strnam1 < strnam2)
                        {
                            str = stri;
                        }
                    }
                }
                int demrow = -1;
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    string sql = "select COUNT(*) as SR FROM tbSudung" + str + " where somoi2 IS NULL";
                    sqlcmd = new SqlCommand(sql, sqlcon);
                    demrow = Convert.ToInt32(sqlcmd.ExecuteScalar());

                    if (demrow == 0)
                    {
                        str = "1-" + str;
                    }
                    else
                    {
                        str = "0-" + str;
                    }
                    sqlcon.Close();
                }
            return str;
        }

        public int updatesokhau(string makh, int sokhau)
        {
            int rtt = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_update_sokhaukh", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@macu", SqlDbType.NVarChar,25).Value = makh;
                    sqlcmd.Parameters.Add("@sokhau", SqlDbType.Int).Value = sokhau;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtt = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtt;
        }
        public int update_khmoi_tbsudung(Int64 idkhnew, string tbsudung)
        {
            int uptt = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("khachhangmoi_update_tbsudung", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkhnew", SqlDbType.BigInt).Value = idkhnew;
                    sqlcmd.Parameters.Add("@tbsudung_para", SqlDbType.NVarChar, 30).Value = tbsudung;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    uptt = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return uptt;
        }

        public int tinhcuocCQluythua(Int64 idkh, int somoi, int kl1, int kl2, int kl3, string tbsudung)
        {
            int tctt = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("khachhangCQ_luyThua_tinhcuoc", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@somoi", SqlDbType.Int).Value = somoi;
                    sqlcmd.Parameters.Add("@kl1", SqlDbType.Int).Value = kl1;
                    sqlcmd.Parameters.Add("@kl2", SqlDbType.Int).Value = kl2;
                    sqlcmd.Parameters.Add("@kl3", SqlDbType.Int).Value = kl3;
                    sqlcmd.Parameters.Add("@tbsudung", SqlDbType.NVarChar,30).Value = tbsudung;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    tctt = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return tctt;
        }
    }
}
